package com.java2007.dao.impl;

import com.java2007.dao.FoodDao;
import com.java2007.pojo.Food;
import com.java2007.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据交互实现层
 */
public class FoodDaoImpl implements FoodDao {
    /**
     * 查询全部
     * @return
     * @throws SQLException
     */
    @Override
    public List<Food> selectAll() throws SQLException {
        //获取执行对象
        QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
        String sql="SELECT `food_id` foodId,`food_name` foodName ,t.`type_id` typeId ,`price`,`vip_price` vipPrice , t.`type_name` typeName FROM `t_food` f,`t_food_type` t WHERE f.`type_id` = t.`type_id` ";
        return  runner.query(sql, new BeanListHandler<Food>(Food.class));
    }

    /**
     * 根据名字查询
     * @param foodName
     * @return
     * @throws SQLException
     */
    @Override
    public List<Food> findByFoodName(String foodName) throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
        String sql="SELECT `food_id` foodId,`food_name` foodName ,t.`type_id` typeId ,`price`,`vip_price` vipPrice , t.`type_name` typeName FROM `t_food` f,`t_food_type` t WHERE f.`type_id` = t.`type_id` AND `food_name` LIKE ? ";
        return runner.query(sql, new BeanListHandler<Food>(Food.class),foodName);
    }

    /**
     * 根据ID删除
     * @param foodId
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteByFoodId(int foodId) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql="DELETE FROM `t_food` WHERE `food_id` = ?";
        return runner.update(JdbcUtil.getConnection(),sql,foodId);
    }

    /**
     * 添加
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public int saveFood(Food food) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql="INSERT INTO `t_food`(`food_name`,`type_id`,`price`,`vip_price`,`image`,`food_desc`,`type_name`)VALUES(?,?,?,?,?,?,?)";
        return runner.update(JdbcUtil.getConnection(),sql,food.getFoodName(),food.getTypeId(),food.getPrice(),food.getVipPrice(),food.getImage(),food.getFoodDesc(),food.getTypeName());
    }

    /**
     * 通过ID查询
     * @param foodId
     * @return
     * @throws SQLException
     */
    @Override
    public Food findByFoodId(int foodId) throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
        String sql="SELECT `food_id` foodId,`food_name` foodName , `type_id` typeId ,`price`,`vip_price` vipPrice ,`image`,`food_desc` foodDesc FROM `t_food` WHERE `food_Id` = ? ";
        return runner.query(sql, new BeanHandler<Food>(Food.class),foodId);
    }

    /**
     * 更新
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public int updataFood(Food food) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql="UPDATE `t_food` SET `food_name` = ? ,`type_id` = ? ,`price` = ?,`vip_price` = ?,`image` = ?,`food_desc` = ? WHERE `food_id` = ?";
        return runner.update(JdbcUtil.getConnection(),sql,food.getFoodName(),food.getTypeId(),food.getPrice(),food.getVipPrice(),food.getImage(),food.getFoodDesc(),food.getFoodId());
    }

    /**
     * 分页查询
     * @param pageNo
     * @param size
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public List<Food> selectByPage(Integer pageNo, Integer size, Food food) throws SQLException {
        //装参数的集合
        List<Object> params=new ArrayList<>();
        QueryRunner qr=new QueryRunner(JdbcUtil.getDataSource());
        StringBuffer sql=new StringBuffer("select `food_id` foodId,`food_name` foodName,`type_name` typeName,`price` price,`vip_price` vipPrice,`image` image,`food_desc` foodDesc from t_food where 1=1 ");
        if (food.getTypeName()!=null){
            sql.append(" and type_name=? ");
            params.add(food.getTypeName());
        }
        if (food.getFoodName()!=null){
            sql.append(" and food_name like ? ");
            params.add("%"+food.getFoodName()+"%");
        }
        sql.append(" limit ?,?");
        params.add(pageNo);
        params.add(size);
        return qr.query(sql.toString(),new BeanListHandler<>(Food.class),params.toArray());
    }

    /**
     * 总页数
     * @param food
     * @return
     * @throws SQLException
     */
    @Override
    public Long selectFoodCount(Food food) throws SQLException {
        //装参数的集合
        List<Object> params=new ArrayList<>();
        QueryRunner qr=new QueryRunner(JdbcUtil.getDataSource());
        StringBuffer sql = new StringBuffer("select count(1) from t_food where 1=1 ");
        if (food.getTypeName()!=null){
            sql.append(" and type_name=? ");
            params.add(food.getTypeName());
        }
        if (food.getFoodName()!=null){
            sql.append(" and food_name=? ");
            params.add(food.getFoodName());
        }
        //封装总页数
        return qr.query(sql.toString(),new ScalarHandler<Long>(),params.toArray());
    }
}
